/*
 * GroupHandler.java
 *
 * Created on February 12, 2007, 11:23 PM
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package com.asaga.tools;

import java.beans.*;
import java.io.Serializable;
import java.util.Date;
import java.util.ArrayList;
import java.sql.*;
import java.util.*;
import org.apache.tools.ant.taskdefs.Java;
import java.io.*;
import java.net.*;
import javax.servlet.*;
import javax.servlet.http.*;

/**
 *
 * @author jeremys
 */
public class GroupHandler {
    private DBPool connectionPool;
    private Connection conn;
    private String errmsg;
    //private List groupListSet;
    /** Creates a new instance of GroupHandler */
    public GroupHandler() {
        errmsg = "no error";
        connectionPool = DBPool.getInstance();
        conn = connectionPool.getConnection();
    }
    
    /*
    public List getGroupList(){

        ResultSet rsGroupList = null;
        String queryGroupList = 
                "select * from autismgroup";   
        groupListSet = new ArrayList();
        
        try{
            java.sql.Statement stGroupList = conn.createStatement();
            rsGroupList = stGroupList.executeQuery(queryGroupList);
            errmsg = rsGroupList.toString();
            Group_DBImpl groupGroupList = null;
            
            while(rsGroupList.next()){
                
                int groupListNumber = rsGroupList.getInt("groupID");              
                groupGroupList = new Group_DBImpl(groupListNumber,conn);
                //errmsg = groupGroupList.toString();
                groupListSet.add(groupGroupList);
                
                
                 
            }
            
        }catch(SQLException ex){
            this.errmsg = ex.toString();
            return null;
        }  
         
        return groupListSet;
    }
    */
    
    public String handleGroupRequest(HttpServletRequest request, HttpSession session){
        
        // SECTION-BEGIN: Method variable declarations
        String groupAction = request.getParameter("groupAction");
        SystemClient_DBImpl systemClient = null;
        AutismGroup autismGroup = null;
        AutismGroupMessage autismGroupMessage = null;
        TreeSet autismGroupMessageTreeSet = null;
        TreeSet autismGroupTreeSet = null;
        AutismGroupList autismGroupList = null;
        String query = null;
        java.sql.Statement statement = null;
        ResultSet rs = null;
        String errorMessage = null;
        int rowCount;
        int userId;
        int groupId;
        int messageId;
        // SECTION-END: Method variable declarations
        
        // SECTION-BEGIN: Get systemClient object from session object
        try{
            systemClient = (SystemClient_DBImpl)session.getAttribute("userProfile");
        }catch(NullPointerException ex){
            session.setAttribute("message",ex.toString());
            return "/jrs_error.jsp";
        }
        // SECTION-END: Get systemClient object from session object
        
        if(groupAction.equals("detail")){

// myGroups            
        }else if(groupAction.equals("myGroups")){
            int numUsers2Validate = 0;
            TreeSet autismAdminGroupTreeSet = new TreeSet();
            TreeSet autismRegularGroupTreeSet = new TreeSet();
            TreeSet autismNoValidGroupTreeSet = new TreeSet();

            userId = systemClient.getId();
            
        //SECTION-BEGIN:get groups that user belongs to
            query = "SELECT * FROM person_has_autismgroup ap, autismgroup a WHERE "
                    +"ap.autismgroup_groupID=a.groupID AND "
                    +"ap.person_personID="+userId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    autismGroup = new AutismGroup();
                    autismGroup.SetGroupId(rs.getInt("groupID"));
                    autismGroup.SetGroupName(rs.getString("name"));
                    autismGroup.SetGroupDescription(rs.getString("description"));                    
                    if(rs.getBoolean("groupCreator")==true ||
                            rs.getBoolean("groupAdmin")==true){
                        autismAdminGroupTreeSet.add(autismGroup);
                    }else if(rs.getBoolean(3)==true){
                        autismRegularGroupTreeSet.add(autismGroup);
                    }else{
                        autismNoValidGroupTreeSet.add(autismGroup);
                    }
                }
                session.setAttribute("autismAdminGroupTreeSet",autismAdminGroupTreeSet);
                session.setAttribute("autismRegularGroupTreeSet",autismRegularGroupTreeSet);
                session.setAttribute("autismNoValidGroupTreeSet",autismNoValidGroupTreeSet);
                
            }catch(SQLException ex){
                session.setAttribute("message","MyGroups group 01:"+ex.toString());
                return "/jrs_error.jsp";                 
            }
        //SECTION-END:get groups that user belongs to  
            
        //SECTION-BEGIN: get number of people awaiting validation
            query = "SELECT COUNT(*) FROM person_has_autismgroup p WHERE "
                    +"p.validate=false AND p.autismgroup_groupID IN "
                    +"(SELECT c.autismgroup_groupID FROM person_has_autismgroup c "
                    +"WHERE c.person_personID= "+userId+" AND c.groupAdmin=true)";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs.next()){
                    numUsers2Validate = rs.getInt(1);
                }else{
                    numUsers2Validate = 0;
                }
                session.setAttribute("numUsers2Validate",String.valueOf(numUsers2Validate));
            }catch(SQLException ex){
                session.setAttribute("message","MyGroups group 01:"+ex.toString());
                return "/jrs_error.jsp";             
            }
        //SECTION-END: get number of people awaiting validation    
            return "/my_groups.jsp";  
            
            
// List all available groups           
        }else if(groupAction.equals("list")){
            autismGroupTreeSet = new TreeSet();
            autismGroupList = new AutismGroupList();
            query = "select * from autismgroup";

            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    autismGroup = new AutismGroup();
                    autismGroup.SetGroupId(rs.getInt("groupID"));
                    autismGroup.SetGroupName(rs.getString("name"));
                    autismGroup.SetGroupDescription(rs.getString("description"));
                    autismGroupTreeSet.add(autismGroup);
                }
                autismGroupList.SetAutismGroupList(autismGroupTreeSet);
                session.setAttribute("autismGroupList",autismGroupList);
                return "/listgroups.jsp";
            }catch(SQLException ex){
                System.out.println(ex.toString());
                return "/portal.jsp";
            }
 
            
// Forum            
        }else if(groupAction.equals("forum")){
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();
            autismGroupMessageTreeSet = new TreeSet();
            
        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
            // SUB-SECTION-BEGIN: check if user is admin/owner
                }else if(rs.getBoolean("groupAdmin")||rs.getBoolean("groupCreator")){
                    session.setAttribute("adminMenu","admin");
                }else{
                    session.setAttribute("adminMenu","noAdmin");
                }
            // SUB-SECTION-END: check if user is admin/owner
            }catch(SQLException ex){
                session.setAttribute("message","Error: forum - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group
        
        // SECTION-BEGIN: get group details
            query = "SELECT * FROM autismgroup WHERE "
                    +"groupID="+groupId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs.next()){
                    autismGroup = new AutismGroup();
                    autismGroup.SetGroupId(rs.getInt("groupID"));
                    autismGroup.SetGroupName(rs.getString("name"));
                    autismGroup.SetGroupDescription(rs.getString("description"));
                }else{
                    autismGroup = null;
                }
                session.setAttribute("autismGroup",autismGroup);
            }catch(SQLException ex){
                session.setAttribute("message","Error: forum - "+ex.toString());
                return "/jrs_error.jsp";             
            }
        // SECTION-END: get group details    
            
        // SECTION-BEGIN: get forum messages
            query = "SELECT a.messageID,a.autismgroup_groupID,a.personID,"
                    +"a.timeCreated,a.messageBody,a.messageSubject,a.parentID,"
                    +"b.fname,b.lname FROM autismgroupmessage a, person b WHERE "
                    +"a.autismgroup_groupID="+groupId
                    +" AND a.personID = b.personID"
                    +" AND a.parentID = 0";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    autismGroupMessage = new AutismGroupMessage();
                    autismGroupMessage.SetMessageId(rs.getInt("messageID"));
                    autismGroupMessage.SetGroupId(rs.getInt("autismgroup_groupID"));
                    autismGroupMessage.SetPersonId(rs.getInt("personID"));
                    autismGroupMessage.SetTimeCreated(rs.getString("timeCreated"));
                    autismGroupMessage.SetMessageBody(rs.getString("messageBody"));
                    autismGroupMessage.SetMessageSubject(rs.getString("messageSubject"));
                    autismGroupMessage.SetParentId(rs.getInt("parentID"));
                    autismGroupMessage.SetPersonName(rs.getString("fname"),rs.getString("lname"));
                    autismGroupMessageTreeSet.add(autismGroupMessage);
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: forum 02 - "+ex.toString());
                return "/jrs_error.jsp";             
            }
            session.setAttribute("autismGroupMessageTreeSet",autismGroupMessageTreeSet);
        // SECTION-END: get forum messages
            return "/group_forum.jsp";      
            
            
// viewMessage
        }else if(groupAction.equals("viewMessage")){    
            groupId = Integer.parseInt(request.getParameter("groupId"));
            messageId = Integer.parseInt(request.getParameter("messageId"));
            userId = systemClient.getId();  
            autismGroupMessageTreeSet = new TreeSet();

        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
            // SUB-SECTION-BEGIN: check if user is admin/owner
                }else if(rs.getBoolean("groupAdmin")||rs.getBoolean("groupCreator")){
                    session.setAttribute("adminMenu","admin");
                }else{
                    session.setAttribute("adminMenu","noAdmin");
                }
            // SUB-SECTION-END: check if user is admin/owner
            }catch(SQLException ex){
                session.setAttribute("message","Error: view message - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group            

        // SECTION-BEGIN: get selected message
            query = "SELECT a.messageID,a.autismgroup_groupID,a.personID,"
                    +"a.timeCreated,a.messageBody,a.messageSubject,a.parentID,"
                    +"b.fname,b.lname FROM autismgroupmessage a, person b WHERE "
                    +" a.personID = b.personID AND "
                    +"a.messageID="+messageId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs.next()){
                    autismGroupMessage = new AutismGroupMessage();
                    autismGroupMessage.SetMessageId(rs.getInt("messageID"));
                    autismGroupMessage.SetGroupId(rs.getInt("autismgroup_groupID"));
                    autismGroupMessage.SetPersonId(rs.getInt("personID"));
                    autismGroupMessage.SetTimeCreated(rs.getString("timeCreated"));
                    autismGroupMessage.SetMessageBody(rs.getString("messageBody"));
                    autismGroupMessage.SetMessageSubject(rs.getString("messageSubject"));
                    autismGroupMessage.SetParentId(rs.getInt("parentID"));
                    autismGroupMessage.SetPersonName(rs.getString("fname"),rs.getString("lname"));
                    autismGroupMessageTreeSet.add(autismGroupMessage);
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: view message 02 - "+ex.toString());
                return "/jrs_error.jsp";             
            }
        // SECTION-END: get selected message            

        // SECTION-BEGIN: get replies to message
            query = "SELECT a.messageID,a.autismgroup_groupID,a.personID,"
                    +"a.timeCreated,a.messageBody,a.messageSubject,a.parentID,"
                    +"b.fname,b.lname FROM autismgroupmessage a, person b WHERE "
                    +"a.personID = b.personID AND "
                    +"a.parentID ="+messageId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    autismGroupMessage = new AutismGroupMessage();
                    autismGroupMessage.SetMessageId(rs.getInt("messageID"));
                    autismGroupMessage.SetGroupId(rs.getInt("autismgroup_groupID"));
                    autismGroupMessage.SetPersonId(rs.getInt("personID"));
                    autismGroupMessage.SetTimeCreated(rs.getString("timeCreated"));
                    autismGroupMessage.SetMessageBody(rs.getString("messageBody"));
                    autismGroupMessage.SetMessageSubject(rs.getString("messageSubject"));
                    autismGroupMessage.SetParentId(rs.getInt("parentID"));
                    autismGroupMessage.SetPersonName(rs.getString("fname"),rs.getString("lname"));
                    autismGroupMessageTreeSet.add(autismGroupMessage);
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: view message 02 - "+ex.toString());
                return "/jrs_error.jsp";             
            }
        // SECTION-END: get selected message
            
        session.setAttribute("autismGroupMessageTreeSet",autismGroupMessageTreeSet);            
        return "/view_message.jsp";
        
// postMessage
        }else if(groupAction.equals("postMessage")){
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();        
            String groupMessageSubject = request.getParameter("groupMessageSubject");
            String groupMessageBody = request.getParameter("groupMessageBody");

        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("postMessage","Error: - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group            
        
        // SECTION-BEGIN: insert new message into database   
            query = "INSERT INTO autismgroupmessage"
                    +" (autismgroup_groupID,personID,messageSubject,messageBody) VALUES "
                    +"('"+groupId+"','"+userId+"','"+groupMessageSubject+"','"+groupMessageBody+"')";
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("postMessage Error: - ",ex.toString());
                return "/jrs_error.jsp";            
            }
        // SECTION-END: insert new message into database
        return "/AutismSiteMasterController?action=group&groupAction=forum&groupId="+groupId;    

// reply2Message
        }else if(groupAction.equals("reply2Message")){
            groupId = Integer.parseInt(request.getParameter("groupId"));
            messageId = Integer.parseInt(request.getParameter("messageId"));
            userId = systemClient.getId(); 
            String groupMessageSubject = request.getParameter("groupMessageSubject");
            String groupMessageBody = request.getParameter("groupMessageBody");
            
        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("postMessage","Error: - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group            

        // SECTION-BEGIN: insert new message into database   
            query = "INSERT INTO autismgroupmessage"
                    +" (autismgroup_groupID,personID,messageSubject,messageBody,parentID) VALUES "
                    +"('"+groupId+"','"+userId+"','"+groupMessageSubject
                    +"','"+groupMessageBody+"','"+messageId+"')";
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("postMessage Error: - ",ex.toString());
                return "/jrs_error.jsp";            
            }
        // SECTION-END: insert new message into database            
            
        return "/AutismSiteMasterController?action=group&groupAction=forum&groupId="+groupId;
        
// listMembers
        }else if(groupAction.equals("listMembers")){
            ResultSet rs2 = null;
            TreeSet AutismGroupMemberList = new TreeSet();
            AutismUser2Validate2Group AutismGroupMember = null;
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();

        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
            // SUB-SECTION-BEGIN: check if user is admin/owner
                }else if(rs.getBoolean("groupAdmin")||rs.getBoolean("groupCreator")){
                    session.setAttribute("adminMenu","admin");
                }else{
                    session.setAttribute("adminMenu","noAdmin");
                }
            // SUB-SECTION-END: check if user is admin/owner
            }catch(SQLException ex){
                session.setAttribute("message","Error: forum - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group            
            
        // SECTION-BEGIN: get list of group members
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"autismgroup_groupID= "+groupId 
                    +" AND validate=1";
           try{
               statement = conn.createStatement();
               rs = statement.executeQuery(query);
               while(rs.next()){
                   AutismGroupMember = new AutismUser2Validate2Group();
                   AutismGroupMember.SetPersonId(rs.getInt("person_personID"));
                   query = "SELECT * FROM person WHERE personID="
                           +AutismGroupMember.GetPersonId();
                   statement = conn.createStatement();
                   rs2 = statement.executeQuery(query);
                   if(rs2.next()){
                       AutismGroupMember.SetEmail(rs2.getString("email"));
                       AutismGroupMember.SetFname(rs2.getString("fname"));
                       AutismGroupMember.SetLname(rs2.getString("lname"));
                       AutismGroupMember.SetGroupId(groupId);
                       AutismGroupMemberList.add(AutismGroupMember);
                   }
               }
           }catch(SQLException ex){
                session.setAttribute("message","Error: list members - "+ex.toString());
                return "/jrs_error.jsp";           
           } 
        // SECTION-END: get list of group members
            session.setAttribute("AutismGroupMemberList",AutismGroupMemberList);
            return "/list_members.jsp";
            
// userList2Validate        
        }else if(groupAction.equals("userList2Validate")){
            userId = systemClient.getId();
            TreeSet autismUser2Validate2GroupTreeSet = new TreeSet();
            AutismUser2Validate2Group autismUser2Validate2Group = null;
            query = "SELECT p.personID, p.fname, p.lname, p.email, a.groupID, a.name "
                    +"FROM person p, person_has_autismgroup pa, autismgroup a WHERE "
                    +"a.groupID=pa.autismgroup_groupID AND "
                    +"p.personID=pa.person_personID AND "
                    +"pa.validate=false AND pa.autismgroup_groupID IN "
                    +"(SELECT c.autismgroup_groupID FROM person_has_autismgroup c "
                    +"WHERE c.person_personID="+userId+" AND c.groupAdmin=true)";
            
         //SECTION-BEGIN: grab user list from db
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    autismUser2Validate2Group = new AutismUser2Validate2Group();
                    autismUser2Validate2Group.SetPersonId(rs.getInt("personID"));
                    autismUser2Validate2Group.SetFname(rs.getString("fname"));
                    autismUser2Validate2Group.SetLname(rs.getString("lname"));
                    autismUser2Validate2Group.SetEmail(rs.getString("email"));
                    autismUser2Validate2Group.SetGroupId(rs.getInt("groupID"));
                    autismUser2Validate2Group.SetGroupName(rs.getString("name"));
                    autismUser2Validate2GroupTreeSet.add(autismUser2Validate2Group);
                }
                session.setAttribute("autismUser2Validate2GroupTreeSet",autismUser2Validate2GroupTreeSet);
            }catch(SQLException ex){
                session.setAttribute("message","Error: userList2Validate - "+ex.toString());
                return "/jrs_error.jsp";            
            }
         //SECTION-END: grab user list from db
            
            return "/user_2_group_validation_list.jsp";
 
// Validate
        }else if(groupAction.equals("validate")){
            int personId = Integer.parseInt(request.getParameter("personId"));
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();
            
        //SECTION-BEGIN: check if user is admin of group
            //query = "SELECT * FROM person_has_autismgroup WHERE "
            //        +"person_personID="+userId+" AND autismgroup_groupID="+groupId
            //        +" AND groupAdmin=true OR groupCreator=true";
            query = "SELECT * FROM (SELECT * FROM person_has_autismgroup WHERE "
                    +"groupAdmin=TRUE OR groupCreator=TRUE) AS table01 WHERE "
                    +"person_personID="+userId+" AND autismgroup_groupID="+groupId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    errorMessage = userId+" is not a valid administrator of group "+groupId;
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: validate 01 - "+ex.toString());
                return "/jrs_error.jsp";             
            }
        //SECTION-END: check if user is admin of group
        
        //SECTION-BEGIN: validate the person
            query = "UPDATE person_has_autismgroup SET "
                    +"validate = true "
                    +"WHERE person_personID="+personId+" AND autismgroup_groupID="+groupId;
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
                if(rowCount<1){
                    errorMessage = "No rows affected by update";
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: validate 02 - "+ex.toString());
                return "/jrs_error.jsp";             
            }            
        //SECTION-END: validate the person
            return "/AutismSiteMasterController?action=group&groupAction=userList2Validate";
            
// Add a Group          
        }else if(groupAction.equals("add")){
            
            String groupName = request.getParameter("groupName");
            String groupDescription = request.getParameter("groupDescription");
            int groupCreatorId = systemClient.getId();
            int newGroupId;
            
            // SECTION-BEGIN:get names of all current groups & check if duplicate
            query = "select * from autismgroup where name='"+groupName+"'";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                while(rs.next()){
                    if(rs.getString("name").equals(groupName)){
                        errorMessage = "Group with name: '"+groupName+"' already exists.";
                        session.setAttribute("message",errorMessage);
                        return "/jrs_error.jsp";
                    }
                }
            }catch(SQLException ex){
                //System.out.println(ex.toString());
                session.setAttribute("message","get names of all current groups & check if duplicate:"+ex.toString());
                return "/jrs_error.jsp";
            }            
            // SECTION-END:get names of all current groups & check if duplicate
            
            // SECTION-BEGIN: Add group to database
            query = "INSERT INTO autismgroup"
                    +" (name,description) VALUES "
                    +"('"+groupName+"','"+groupDescription+"')";
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("message","Add group to database:"+ex.toString());
                return "/jrs_error.jsp";            
            }
            
            //SUB-SECTION-BEGIN: get new groupId
            query = "select * from autismgroup WHERE "
                    +"name='"+groupName+"'";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs.next()){
                    newGroupId = rs.getInt("groupID");
                }else{
                    errorMessage = "Group with name: "+groupName+" already exists.";
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";
                }
            }catch(SQLException ex){
                session.setAttribute("message","get new groupId:"+ex.toString());
                return "/jrs_error.jsp";
            }   
            //SUB-SECTION-END: get new groupId
            
            //SUB-SECTION-BEGIN: insert creator into person_has_autismgroup table
            query = "INSERT INTO person_has_autismgroup"
                    +" (person_personID,autismgroup_groupID,validate,groupAdmin,groupCreator)"
                    +" VALUES ("+groupCreatorId+","+newGroupId+",1,1,1)";
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("message",ex.toString());
                return "/jrs_error.jsp";                
            }
            //SUB-SECTION-END: insert creator into person_has_autismgroup table
            
            //SECTION-END: Add group to database
            return "/AutismSiteMasterController?action=group&groupAction=list";
            
        }else if(groupAction.equals("commit")){
            
            
// remove: Remove a user from a group            
        }else if(groupAction.equals("remove")){
            int personId = Integer.parseInt(request.getParameter("personId"));
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();

        //SECTION-BEGIN: check if user is admin of group
            //query = "SELECT * FROM person_has_autismgroup WHERE "
            //        +"person_personID="+userId+" AND autismgroup_groupID="+groupId
            //        +" AND groupAdmin=true OR groupCreator=true";
            query = "SELECT * FROM (SELECT * FROM person_has_autismgroup WHERE "
                    +"groupAdmin=TRUE OR groupCreator=TRUE) AS table01 WHERE "
                    +"person_personID="+userId+" AND autismgroup_groupID="+groupId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    errorMessage = userId+" is not a valid administrator of group "+groupId;
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("message","Error: validate 01 - "+ex.toString());
                return "/jrs_error.jsp";             
            }
        //SECTION-END: check if user is admin of group
        //SECTION-BEGIN: check if user is trying to delete himself/herself
            if(userId==personId){
                session.setAttribute("message","Remove from group error: You cannot remove yourself!");
                return "/jrs_error.jsp";
            }
        //SECTION-END: check if user is trying to delete himself/herself
        //SECTION-BEGIN: remove user from group    
        query = "DELETE FROM person_has_autismgroup WHERE "
                +"person_personID="+personId+" AND autismgroup_groupID="+groupId;
        try{
            statement = conn.createStatement();
            rowCount = statement.executeUpdate(query);
        }catch(SQLException ex){
                session.setAttribute("message","Remove from group 01:"+ex.toString());
                return "/jrs_error.jsp";        
        }
        //SECTION-END: remove user from group     
        return "/AutismSiteMasterController?action=group&groupAction=listMembers&groupId="+groupId;
        
// Join            
        }else if(groupAction.equals("join")){
            int group2JoinId = Integer.parseInt(request.getParameter("group2Join"));
            userId = systemClient.getId();
            
            //SECTION-BEGIN: check to see if already part of group
            query = "SELECT * from person_has_autismgroup WHERE "
                    + "person_personID="+userId
                    +" AND autismgroup_groupID="+group2JoinId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs.next()){
                    errorMessage = "User ID "+userId+" is already part of this group.";
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";
                }
            }catch(SQLException ex){
                session.setAttribute("message","Join group 01:"+ex.toString());
                return "/jrs_error.jsp";              
            }
            //SECTION-END: check to see if already part of group
            
            //SECTION-BEGIN: add user to group
            query = "INSERT INTO person_has_autismgroup"
                    +" (person_personID,autismgroup_groupID) VALUES "
                    +"("+userId+","+group2JoinId+")";
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("message","Join group 02:"+ex.toString());
                return "/jrs_error.jsp";            
            }        
            //SECTION-END: add user to group
            return "/AutismSiteMasterController?action=group&groupAction=myGroups";
       
            
// removeMessage: Remove a Message from a Group            
        }else if(groupAction.equals("removeMessage")){
            groupId = Integer.parseInt(request.getParameter("groupId"));
            messageId = Integer.parseInt(request.getParameter("messageId"));
            userId = systemClient.getId(); 
            
        // SECTION-BEGIN: check to see if user is admin or owner of group
            query = "SELECT * FROM (SELECT * FROM person_has_autismgroup WHERE "
                    +"groupAdmin=TRUE OR groupCreator=TRUE) AS table01 WHERE "
                    +"autismgroup_groupID="+groupId+" AND person_personID="+userId;
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(rs == null){
                    errorMessage = "0 You do not have valid permission to remove message."+rs.toString();
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";                                
                }else if(!rs.next()){
                    errorMessage = "1 You do not have valid permission to remove message."+rs.getFetchSize()
                    +" "+rs.getInt("person_personID");
                    session.setAttribute("message",errorMessage);
                    return "/jrs_error.jsp";                     
                }
                }catch(SQLException ex){
                session.setAttribute("message","Remove Message 01:"+ex.toString());
                return "/jrs_error.jsp";
            }
        // SECTION-END: check to see if user is admin or owner of group
            
        // SECTION-BEGIN: remove the message from the database.
            query = "DELETE FROM autismgroupmessage WHERE "
                    +"messageID="+messageId;
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("message","Remove Message 02:"+ex.toString());
                return "/jrs_error.jsp";
            }        
        // SECTION-END: remove the message from the database.    
            
            return "/AutismSiteMasterController?action=group&groupAction=forum&groupId="+groupId;
            
// leaveGroup: Leave a Group            
        }else if(groupAction.equals("leaveGroup")){
            groupId = Integer.parseInt(request.getParameter("groupId"));
            userId = systemClient.getId();    

        // SECTION-BEGIN: check if user is valid member of group
            query = "SELECT * FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId
                    +" AND validate=true";
            try{
                statement = conn.createStatement();
                rs = statement.executeQuery(query);
                if(!rs.next()){
                    session.setAttribute("message",userId+" is not a valid member of group "+groupId);
                    return "/jrs_error.jsp";                    
                }
            }catch(SQLException ex){
                session.setAttribute("postMessage","Error: - "+ex.toString());
                return "/jrs_error.jsp";              
            }
        // SECTION-END: check if user is valid member of group
            
        // SECTION-BEGIN: remove association in person_has_autismgroup table 
            query = "DELETE FROM person_has_autismgroup WHERE "
                    +"person_personID="+userId+" AND "
                    +"autismgroup_groupID="+groupId;
            try{
                statement = conn.createStatement();
                rowCount = statement.executeUpdate(query);
            }catch(SQLException ex){
                session.setAttribute("message","Remove Message 02:"+ex.toString());
                return "/jrs_error.jsp";
            }            
        // SECTION-END: remove association in person_has_autismgroup table    
            
            return "/AutismSiteMasterController?action=group&groupAction=myGroups";
            
        }else{
            return "/portal.jsp";
        }
        return "/portal.jsp";
    }
    
    
    public String toString(){
        return this.errmsg;
    }
    
    
            
    
    
}
